﻿CREATE TABLE [CA].[Goods] (
    [Id]                 INT             NOT NULL,
    [GId]                INT             NULL,
    [AssortmentId]       INT             CONSTRAINT [DF__Goods__Assortmen__44CA3770] DEFAULT ((1)) NOT NULL,
    [ManufacturerId]     INT             CONSTRAINT [DF__Goods__Manufactu__45BE5BA9] DEFAULT ((1)) NOT NULL,
    [CountryId]          INT             CONSTRAINT [DF__Goods__CountryId__46B27FE2] DEFAULT ((1)) NOT NULL,
    [WholesalePack]      INT             CONSTRAINT [DF_Goods_WholesalePack] DEFAULT ((1)) NOT NULL,
    [RetailPack]         INT             CONSTRAINT [DF_Goods_RetailPack] DEFAULT ((1)) NOT NULL,
    [AllowUnPack]        BIT             CONSTRAINT [DF_Goods_AllowUnPack] DEFAULT ((0)) NOT NULL,
    [ChildId]            INT             NULL,
    [ManufactureBarCode] VARCHAR (13)    NULL,
    [FixedPrice]         DECIMAL (19, 2) NULL,
    [DefekturaAggregate] BIT             CONSTRAINT [DF_Goods_DefekturaAggregate] DEFAULT ((0)) NOT NULL,
    [Note]               VARCHAR (250)   NULL,
    [MD]                 DATETIME        CONSTRAINT [DF__Goods__MD__47A6A41B] DEFAULT (getdate()) NOT NULL,
    [MW]                 VARCHAR (50)    CONSTRAINT [DF__Goods__MW__489AC854] DEFAULT (suser_sname()) NOT NULL,
    [ToDelete]           BIT             CONSTRAINT [DF__Tmp_Goods__ToDel__6379A719] DEFAULT ((0)) NOT NULL,
    CONSTRAINT [PK_Goods] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (FILLFACTOR = 80),
    CONSTRAINT [FK_Goods_Assortment] FOREIGN KEY ([AssortmentId]) REFERENCES [CA].[Assortment] ([Id]) ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATION,
    CONSTRAINT [FK_Goods_ChildGoods] FOREIGN KEY ([ChildId]) REFERENCES [CA].[Goods] ([Id]) NOT FOR REPLICATION,
    CONSTRAINT [FK_Goods_Countries] FOREIGN KEY ([CountryId]) REFERENCES [CO].[Countries] ([Id]) NOT FOR REPLICATION,
    CONSTRAINT [FK_Goods_Manufacturers] FOREIGN KEY ([ManufacturerId]) REFERENCES [CO].[Manufacturers] ([Id]) ON UPDATE CASCADE NOT FOR REPLICATION
);


GO
CREATE NONCLUSTERED INDEX [IX_Goods_ChildId]
    ON [CA].[Goods]([ChildId] ASC) WITH (FILLFACTOR = 80);


GO
CREATE NONCLUSTERED INDEX [IX_Goods_UniqeGoods]
    ON [CA].[Goods]([AssortmentId] ASC, [ManufacturerId] ASC, [CountryId] ASC, [RetailPack] ASC) WITH (FILLFACTOR = 80);


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Количество в оптовой упаковке, которую продают поставщики', @level0type = N'SCHEMA', @level0name = N'CA', @level1type = N'TABLE', @level1name = N'Goods', @level2type = N'COLUMN', @level2name = N'WholesalePack';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Количество в упаковке, которую можно разделить при продаже', @level0type = N'SCHEMA', @level0name = N'CA', @level1type = N'TABLE', @level1name = N'Goods', @level2type = N'COLUMN', @level2name = N'RetailPack';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Разрешено разделять упаковки', @level0type = N'SCHEMA', @level0name = N'CA', @level1type = N'TABLE', @level1name = N'Goods', @level2type = N'COLUMN', @level2name = N'AllowUnPack';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Ссылка на товар, на который можно разбирать текущий товар', @level0type = N'SCHEMA', @level0name = N'CA', @level1type = N'TABLE', @level1name = N'Goods', @level2type = N'COLUMN', @level2name = N'ChildId';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Штрих код производителя (EAN13)', @level0type = N'SCHEMA', @level0name = N'CA', @level1type = N'TABLE', @level1name = N'Goods', @level2type = N'COLUMN', @level2name = N'ManufactureBarCode';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Розничная цена по умолчанию', @level0type = N'SCHEMA', @level0name = N'CA', @level1type = N'TABLE', @level1name = N'Goods', @level2type = N'COLUMN', @level2name = N'FixedPrice';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Агрегировать аналогичные товары при расчете дефектуры (одинаковые AsortmentId и RetailPack, разные ManufacturerId и CountryId)', @level0type = N'SCHEMA', @level0name = N'CA', @level1type = N'TABLE', @level1name = N'Goods', @level2type = N'COLUMN', @level2name = N'DefekturaAggregate';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Признак того, что запись нужно удалить из БД всех подразделений', @level0type = N'SCHEMA', @level0name = N'CA', @level1type = N'TABLE', @level1name = N'Goods', @level2type = N'COLUMN', @level2name = N'ToDelete';

